﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace CommClass
{
    class CaigouDAO
    {
        private DBSource ds;
        public CaigouDAO(DBSource dbSource)
        {
            this.ds = dbSource;
        }

        //获取采购类型
        public DataTable getcaigoutype()
        {
            DataSet dt = new DataSet();
            string sqlstr = " SELECT [CaigouTypeID],[CaigouTypemiaoshu] FROM [t_CaigouType] order by [CaigouTypeID] ";
            dt = ds.GetRecord(sqlstr);
            return dt.Tables[0];
        }


        //根据筛选条件查询入库单数据
        public DataTable getCaigouData(int pageSize, int currentPage, string strWhere, string filedOrder)
        { 
            DataSet dt = new DataSet();
            int topSize = pageSize * currentPage;
            StringBuilder strSql = new StringBuilder();

            strSql.Append("select top  " + pageSize + "  a.[CaigouID] 采购单id ,a.[CaigouNumber] 采购单号,a.[ZhiDanDate] 制单日期,b.[UserName] 制单人 ,c.[CaigouTypemiaoshu] 采购类型,e.[SupplierName] 货主,g.[SupplySupplierName] 供应商,f.[CarrierName] 承运商 ,h.[StoreName] 仓库,a.[TuiHuoKeHu] 退货客户,d.[CaigouStatusmiaoshu] 采购单状态,a.[CaigouStatusID] 状态id ");
            strSql.Append(" from  [t_Caigou] a left join [t_Carrier] f on f.[CarrierID] = a.[CarrierID], [t_User] b,[t_CaigouType] c ,[t_CaigouStatus] d,[t_Supplier] e ,[t_SupplySupplier] g ,[t_Store] h ");
            strSql.Append(" where a.[UserID] = b.[UserID] and c.[CaigouTypeID]= a.[CaigouTypeID] and d.[CaigouStatusID] = a.[CaigouStatusID] and e.[SupplierID] = a.[SupplierID]   and g.[SupplySupplierID] = a.[SupplySupplierID] and h.[StoreID] = a.[StoreID]  ");
            strSql.Append(" and a.[CaigouID] not in(select top " + topSize + " a.CaigouID from [t_Caigou] a  ");
            if (strWhere != null && strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder + ")");
            if (strWhere != null && strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            dt = ds.GetRecord(strSql.ToString());
            return dt.Tables[0];
        }


        //获取商品分页信息
        public int getCaigoucount(string strWhere)
        { 
            int carrierid;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*)     from [t_Caigou] a  ");
            if (strWhere.Trim() != "")
            {
                strSql.Append("where" + strWhere);
            }
            DataTableReader dr = ds.GetRecord(strSql.ToString()).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
            {
                carrierid = 0;
            }
            else
            {
                carrierid = Int32.Parse(dr[0].ToString());
            }
            return carrierid;

        }



        //查询需要越库的详情
        public DataTable getYuekuData()
        {
            DataSet dt = new DataSet();
         
            StringBuilder strSql = new StringBuilder();

            strSql.Append("   select  a.[SupplierID],a.StoreID, a.[Stock],a.[ProdName],a.[ProdID] ,a.ProdTypeID ,a.needqty,isnull(b.yifenpeiQty,0) yifenpeiQty ,isnull(c.kucunqty,0) kucunqty ,isnull(d.CaigouShuLiang,0) CaigouShuLiang, f.SupplySupplierID,f.[caigoujia],j.StorageID  from      ");
            strSql.Append(" ( select b.[SupplierID],e.StoreID, b.[Stock],x.[ProdName],a.[ProdID] ,x.ProdTypeID ,sum(a.[OrderQty]) needqty from  [t_DOEntry] a  ,[t_DO] b ,[t_DOStatus] c,t_Product x ,[t_Store] e  where  a.DeliveryNumber = b.DeliveryNumber and b.StatusID = c.StatusID and a.[ProdID] =x.[ProdID]    and b.[Stock] = e.[StoreName]   and  c.StatusID<2   group by  a.[ProdID] ,x.[ProdName], b.[Stock],e.StoreID,x.ProdTypeID,b.[SupplierID] )  a  left join  ");
            strSql.Append("  (select  b.[Stock],a.[ProdID], sum(d.StockQty) yifenpeiQty from [t_DOEntry] a,[t_DO] b ,[t_DOStatus] c,[t_chukukucun] d  where  a.DeliveryNumber = b.DeliveryNumber and b.StatusID = c.StatusID and a.[DOEntryID] = d.[DOEntryID] and d.StockTakingid !=0    and  c.StatusID<2 group by a.[ProdID] , b.[Stock]) b  on a.[Stock]=b.[Stock] and a.[ProdID]=b.[ProdID] left join       ");
            strSql.Append("     (select sum(a.StockUseableQty) kucunqty,c.[StoreID],a.[ProdID]  from [t_StockTaking] a,[t_Storage] b,[t_Kuqu] c  where a.[StorageID] = b.[StorageID] and b.[KuquID] = c.[KuquID] and b.[freeze]=0 and  a.StockPinZhiID = 1   group by a.[ProdID] , c.[StoreID] having sum(a.StockUseableQty)>0) c on  a.[StoreID]=c.[StoreID] and a.[ProdID]=c.[ProdID] left join     ");
            strSql.Append("    ( select b.[StoreID],sum(a.CaigouShuLiang) CaigouShuLiang ,a.ProdID from t_CaigouEntry a,t_Caigou b where a.CaigouID = b.CaigouID and b.CaigouStatusID =1 and b.CaigouTypeID = 2  group by a.[ProdID] , b.[StoreID] having sum(a.CaigouShuLiang)>0) d on  a.[StoreID]=d.[StoreID] and a.[ProdID]=d.[ProdID]     ");
            strSql.Append("     ,t_SupplySupplierProduct f  ,t_SupplySupplier j   ");
            strSql.Append("    where  a.[ProdID]=f.[ProdID] and  f.SupplySupplierID = j.SupplySupplierID and   f.shangpinshuxingID=2 and f.youxianji =1  and  j.SupplySupplierStatusID =1   and  (a.needqty - isnull(b.yifenpeiQty,0)-isnull(c.kucunqty,0)-isnull(d.CaigouShuLiang,0))>0    ");
            strSql.Append("    order by f.SupplySupplierID,a.[SupplierID],a.[ProdID]       ");
           
            

            dt = ds.GetRecord(strSql.ToString());
            return dt.Tables[0];
        }



        //获取采购单
        public DataTable GetCaigou(string where)  
        {
            DataSet dt = new DataSet();
            StringBuilder sqlstr1 = new StringBuilder();
            sqlstr1.Append("  SELECT a.[CaigouID],a.[CaigouNumber],a.[ZhiDanDate],a.[CaigouDate],b.[UserName] zhidanren,c.[UserName] caigouren ");
            sqlstr1.Append("   ,d.[CaigouTypemiaoshu],e.[CaigouStatusmiaoshu],f.[SupplierName],g.SupplySupplierName,h.[CarrierName],i.[StoreName]  ,a.[TuiHuoKeHu],a.[TuiHuoRemark],a.[Remark]  ");
            sqlstr1.Append("   FROM   [t_Caigou] a left join [t_Carrier] h on a.[CarrierID] = h.[CarrierID],  [t_User] b ,[t_User] c ,[t_CaigouType] d ,[t_CaigouStatus] e ,[t_Supplier] f  ,[t_SupplySupplier] g,[t_Store] i  ");
            sqlstr1.Append("   where  a.[UserID] = b.[UserID] and a.[CaigourenID] = c.[UserID] and a.[CaigouTypeID] = d.[CaigouTypeID]  ");
            sqlstr1.Append("  and a.[CaigouStatusID] = e.[CaigouStatusID] and a.[SupplierID] = f.[SupplierID] and a.[SupplySupplierID] = g.[SupplySupplierID]  ");
            sqlstr1.Append(" and a.[StoreID] = i.[StoreID]  ");
            if (where != null && where != "")
            {
                sqlstr1.Append(" and " + where);
            }

            dt = ds.GetRecord(sqlstr1.ToString());
            return dt.Tables[0];

        }


        //获取采购单
        public DataTable GetCaigouupdate(string where) 
        {
            DataSet dt = new DataSet();
            StringBuilder sqlstr1 = new StringBuilder();
            sqlstr1.Append("  SELECT a.[CaigouID],a.[CaigouNumber],a.[ZhiDanDate],a.[CaigouDate],a.[UserID],b.[UserName] ,a.[CaigouTypeID],a.[CaigouStatusID],a.[SupplierID],a.[SupplySupplierID],a.[CarrierID],a.[CaigourenID],a.[StoreID],a.[TuiHuoKeHu],a.[TuiHuoRemark],a.[Remark] FROM  [t_Caigou] a ,[t_User] b  where  a.[UserID] = b.[UserID] ");
          
            if (where != null && where != "")
            {
                sqlstr1.Append(" and " + where);
            }

            dt = ds.GetRecord(sqlstr1.ToString());
            return dt.Tables[0];

        }

        //获取采购单明细
        public DataTable GetCaigouEntry(string where)  
        {
            DataSet dt = new DataSet();
            StringBuilder sqlstr1 = new StringBuilder();
            sqlstr1.Append(" select b.[ProdTypeName] 商品种类,c.[ProdName] 商品名称,c.[ProdCode] 商品编码,a.[CaigouShuLiang] 采购数量,a.[Caigoujia]  采购价,convert(decimal(12,4),(a.[CaigouShuLiang]*a.[Caigoujia])) 采购金额 ,c.[Unit] 单位 ,c.[ProdType] 规格,c.[Weight] 吨位,a.[ProdID] 商品id,a.[CaigouEntryID] 明细id,b.[ProdTypeID] 商品种类id  from [t_CaigouEntry] a,[t_ProductType] b ,[t_Product] c   where c.[ProdTypeID]=b.[ProdTypeID] and c.[ProdID] = a.[ProdID]   ");
            if (where != null && where != "")
            {
                sqlstr1.Append(" and " + where);
            }

            dt = ds.GetRecord(sqlstr1.ToString());
            return dt.Tables[0];

        }



        //获取采购单打印
        public DataTable GetCaigouprint(string where)  
        {
            DataSet dt = new DataSet();
            StringBuilder sqlstr1 = new StringBuilder();
            sqlstr1.Append(" select  a.CaigouNumber,b.SupplySupplierName,b.dizhi,d.fukuanmiaoshu,c.shouhuoshuxinmiaoshu,b.yinhangzhanghao   ");
            sqlstr1.Append(" ,b.lianxifangshi,e.StoreAddress,a.CaigouDate,g.ProdTypeName,h.ProdType,f.CaigouShuLiang,f.Caigoujia,f.CaigouShuLiang*f.Caigoujia jine,f.Remark,h.ProdName   ");
            sqlstr1.Append("  from t_Caigou a ,t_SupplySupplier b,t_shouhuoshuxing c,t_fukuan d,t_Store e,t_CaigouEntry f,[t_ProductType] g ,[t_Product] h  ");
            sqlstr1.Append(" where a.SupplySupplierID = b.SupplySupplierID and b.shouhuoshuxingID = c.shouhuoshuxingID    ");
            sqlstr1.Append(" and b.fukuanID = d.fukuanID and a.StoreID = e.StoreID  and a.CaigouID = f.CaigouID   ");
            sqlstr1.Append(" and g.[ProdTypeID]=h.[ProdTypeID] and f.[ProdID] = h.[ProdID]   ");
            if (where != null && where != "")
            {
                sqlstr1.Append(" and " + where);
            }

            dt = ds.GetRecord(sqlstr1.ToString());
            return dt.Tables[0];

        }


        //获取供应商商品分页信息
        public DataTable getproduct(int pageSize, int currentPage, string strWhere, string filedOrder)
        {
            DataSet dt = new DataSet();

            int topSize = pageSize * currentPage;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top  " + pageSize + " a.[ProdID] as 商品id,b.ProdTypeName as 商品类型,a.[ProdCode] as 商品编码,a.[ProdName] as 商品名称,c.[caigoujia] 采购价,c.youxianji 优先级 ,a.[ProdType] as 规格 ,a.[Weight] as 商品重量,a.[ProdTypeID] as 商品类型id,a.[SupplierID] as 货主id  ");


            strSql.Append(" from  t_Product a ,t_ProductType b,t_SupplySupplierProduct c  ");
            strSql.Append(" where a.ProdTypeID = b.ProdTypeID and a.ProdID = c.ProdID and a.ProdID not in(select top " + topSize + " a.ProdID from t_Product a,t_ProductType b,t_SupplySupplierProduct c where a.ProdTypeID = b.ProdTypeID and a.ProdID = c.ProdID  ");

            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            strSql.Append(" order by " + filedOrder + ")");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            dt = ds.GetRecord(strSql.ToString());
            return dt.Tables[0];
        }


        //获取商品分页信息
        public int getproductcount(string strWhere)
        {
            int carrierid;
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) carrierid  from t_Product a ,t_ProductType b,t_SupplySupplierProduct c  where a.ProdTypeID = b.ProdTypeID and a.ProdID = c.ProdID ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            DataTableReader dr = ds.GetRecord(strSql.ToString()).CreateDataReader();
            dr.Read();
            if (!dr.HasRows)
            {
                carrierid = 0;
            }
            else
            {
                carrierid = Int32.Parse(dr[0].ToString());
            }
            return carrierid;

        }





        //获取系统补货建议
        public DataTable getbuhuoproduct(  string strWhere, string filedOrder)
        {   
            DataSet dt = new DataSet();

            
            StringBuilder strSql = new StringBuilder();
            strSql.Append(" select   a.[ProdID] as 商品id,b.ProdTypeName as 商品类型,a.[ProdCode] as 商品编码,a.[ProdName] as 商品名称,m.SupplySupplierName 供应商,c.[caigoujia] 采购价,c.youxianji 优先级 ,a.[ProdType] as 规格 ,a.[Weight] as 商品重量,a.[ProdTypeID] as 商品类型id,a.[SupplierID] as 货主id ,g.OrderQty/7 as 平均,a.anquantianshu as 安全天数 , a.zuidatianshu as 最大天数,isnull(h.StockUseableQty,0) 可用库存, isnull(l.[CaigouShuLiang],0) 采购数量,c.SupplySupplierID 供应商id,a.SupplierID 货主,h.StoreID 仓库id , CEILING(a.zuidatianshu*g.OrderQty/7) 预计采购   ");


            strSql.Append("  from  t_Product a ,t_ProductType b,t_SupplySupplierProduct c,t_SupplySupplier m ,(select e.ProdID,z.StoreID ,sum(isnull(e.OrderQty,0)) OrderQty from t_DO d,t_DOEntry e,t_Product f,t_Store z  where d.Stock = z.StoreName and d.[DeliveryNumber] = e.[DeliveryNumber] and e.ProdID = f.ProdID  and f.anquantianshu>0 and datediff(day,[OrderDatetime],(getdate()-1))<7  group by  e.ProdID,z.StoreID  ) g left join ( select a.[ProdID], sum(isnull(a.StockUseableQty,0)) StockUseableQty,c.StoreID from t_StockTaking a,t_Storage b,t_Kuqu c where a.StorageID = b.StorageID and b.KuquID = c.KuquID and StockPinZhiID = 1 group by a.ProdID,c.StoreID ) h on g.ProdID=h.ProdID and g.StoreID=h.StoreID  left join (select k.ProdID,sum(isnull(k.[CaigouShuLiang],0))  CaigouShuLiang ,j.StoreID from  [t_Caigou] j,t_CaigouEntry k where j.[CaigouID] = k.[CaigouID] and  j.CaigouStatusID = 1 and j.CaigouTypeID =1 group by k.ProdID,j.StoreID  ) l on g.ProdID=l.ProdID  and g.StoreID = l.StoreID    ");
            strSql.Append(" where  a.ProdTypeID = b.ProdTypeID and a.ProdID = c.ProdID  and a.[ProdID]= g.ProdID    and c.SupplySupplierID = m.SupplySupplierID and c.youxianji = 1 and c.[shangpinshuxingID] = 1 and (g.OrderQty/7*anquantianshu - isnull(h.StockUseableQty,0) -isnull(l.[CaigouShuLiang],0)  )>=0  ");

          
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            dt = ds.GetRecord(strSql.ToString());
            return dt.Tables[0];
        }

        //获取采购主单
        public DataTable getcaigoutzhudan(string strWhere)
        {
            DataSet dt = new DataSet();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("  SELECT  a.[CaigouID], a.[CaigouNumber],a.[ZhiDanDate],a.[CaigouDate],a.[UserID],a.[CaigouTypeID],a.[CaigouStatusID],a.[SupplierID],a.[SupplySupplierID],a.[CarrierID],a.[CaigourenID],a.[StoreID],b.[StoreName],a.[TuiHuoKeHu],a.[TuiHuoRemark],a.[Remark] ,c.[SupplySupplierName],c.[lianxifangshi],c.[dizhi]  FROM  [t_Caigou] a ,[t_Store] b ,[t_SupplySupplier] c   where a.[StoreID] = b.[StoreID] and a.[SupplySupplierID] = c.[SupplySupplierID]  ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            dt = ds.GetRecord(strSql.ToString());
            return dt.Tables[0];
        }

        //获取采购主单
        public DataTable getcaigoutmingxi(string strWhere) 
        {
            DataSet dt = new DataSet();
            StringBuilder strSql = new StringBuilder();
            strSql.Append("  SELECT a.[CaigouEntryID],a.[CaigouID],a.[ProdID],a.[CaigouShuLiang],a.[Remark],a.[Caigoujia],c.[ProdTypeName] ,b.[ProdName],b.[Unit],b.[ProdCode],b.[Weight],b.[Volume],b.[ProdTypeID]   FROM  [t_CaigouEntry] a,[t_Product] b,[t_ProductType] c   where   a.[ProdID] = b.[ProdID] and b.[ProdTypeID] = c.[ProdTypeID] ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" and " + strWhere);
            }
            dt = ds.GetRecord(strSql.ToString());
            return dt.Tables[0];
        }
       

    }


 }

    



